04. Set Up Your Local Environment

Let's set up your local environment

What is PostgreSQL?

PostgreSQL is an object-relational database management system. Object-relational databases use a hybrid approach to databases.

1. In **object databases**, information is stored as objects, much like object-oriented programming.
2. In **relational databases**, information is stored in tables with relationships between tables defined by primary and foreign keys.

Importantly, PostgreSQL allows the use of advanced functions (such as Window Functions), and even development and use of custom functions written in different programming languages. Here is a link to better understand what is meant by an object-relational database, and how it differs from a relational database.

https://en.wikipedia.org/wiki/Object-relational_database

Ready to proceed with PostgreSQL? Follow along!

Step 1. Downloading PostgreSQL

First, you will need to install PostgreSQL on your local machine. Select the following three components during installation - PostgreSQL server, pgAdmin, and command-line tools. pgAdmin is a GUI tool for managing the database. Download the installer from the link below, and install with the admin privileges:

• Installing PostgreSQL for Windows:
  https://www.postgresql.org/download/windows/ and follow the steps mentioned here
  
  

• Installing PostgreSQL for Mac OS:
  
  https://www.postgresql.org/download/macosx/ and follow the steps mentioned here

Friendly reminder! Please write down the database superuser (postgres) password as you will need it to create the Sakila database once you have installed the PostgreSQL server.

• Optionally, you can verify the successful installation of PostgreSQL using the following commands in your terminal:

# Server version:
pg_config --version
# Client version:
psql --version

Step 2. Downloading Sakila database

Once PostgreSQL server is installed, you will need to download the Movie database from this page: PostgreSQL Sample Database

Scroll down and click on the orange "Download DVD Rental Sample Database" button.

This will download a zipped file, and you will need to extract the ** dvdrental.tar file**.

Step 3. Connect to the PostgreSQL server

Launch pgAdmin tool, a graphical tool for managing and developing PostgreSQL databases. The PostgreSQL interactive installer by EDB includes the pgAdmin by default. If you have a native PostgreSQL installation, you can download pgAdmin from here. It will take some time to launch the pgAdmin tool. Let's connect the pgAdmin to the PostgreSQL server.

• Within the Object browser (left-navigation pane), right-click on the Servers → Create → Server… option.

* It will open up a dialog box asking for the PostgreSQL server name, and credentials for the “postgres" superuser. See the snapshot below.

• Provide the following details, and leave the other fields as default.

Step 4. Loading database

The next step is to load the DVD Rental database into your PostgreSQL server on your machine.
Follow the instructions on the page - Load PostgreSQL Sample Database (scroll down ⅓ on this page). There are two ways to load the database:

1. Load the sample database using psql tool
2. Load the DVD Rental database using the pgAdmin

We will use the Load the DVD Rental database using pgAdmin tool method. See the snapshot below.

The general steps to load the sample database into your local PostgreSQL database server are:

1. Create a blank database, with the name dvdrental.

2. Restore the data from the locally downloaded Sakila database.

3. You need to have either the unzipped data folder or the tar format file locally. Choose the desired source format and the path of the file/folder.

Step 5. Choose the DVD Rental database

Once, you have restored the database, you are all set to use it. Choose the dvdrental database under Databases (left-navigation pane).

Step 6. Running Queries on your dvdrental database

Ready to run some queries??

Open the Query tool, write your queries in the Query editor, and run them to see your data output. See the snapshots below.